package exquery

import exquery.util.WildCardUtil
import exquery.valuebeans.genericsearch.OrderByClauseVO
import exquery.valuebeans.genericsearch.WhereClauseVO
import org.apache.log4j.Logger
import org.codehaus.groovy.grails.plugins.web.taglib.ApplicationTagLib
import setupx.entity.SearchConfig
import taxonomy.NCBITaxonomyService
import taxonomy.TaxonomyNodes

class GenericSearchService {

    boolean transactional = true
    Logger gLogger = Logger.getLogger(GenericSearchService.class)

    def executeQueryService = new dal.ExecuteQueryService()
    WildCardUtil wildCard = WildCardUtil.getInstance();
    def g = new ApplicationTagLib()

    def speciesService
    def taxonomyService = new NCBITaxonomyService()

   /**
    * to get all option list for select clause 
    * @return
   */
  Collection<SearchConfig> getAllSelectOptions() {
    gLogger.info "Comming into getAllSelectOptions method of GenericSearchService"
    Collection<SearchConfig> resultCollection  = null;

    resultCollection = SearchConfig.executeQuery("Select sc from setupx.entity.SearchConfig sc where sc.groupkey != 'OBJ' order by sc.orderBy")

    gLogger.info "Out from getAllSelectOptions method of GenericSearchService"
    return resultCollection
  }

  /**
   * to process search request  
   * @param selectList
   * @param whereClauseVOList
   * @param orderByClauseVOList
   * @return
   */
  Collection processQuery(def selectList,def whereClauseVOList,def orderByClauseVOList){
    gLogger.info "Comming into processQuery method of GenericSearchService"

    String selectString = " E.experiment_id ";
    String fromString = "";
    String whereString = "";
    String orderByString = "";

    /* prepare select clause : start */

    gLogger.info "selectList :: ${selectList.class}"

    if(selectList instanceof String[] || selectList instanceof Collection){
       selectList.each {str->
          String asString = str.toString().trim().toUpperCase()
          asString = asString.replaceAll("[.]","_")

          selectString += ", ${str} AS \"${asString}\""
       }
    } else{
      String asString = selectList.toString().trim().toUpperCase()
      asString = asString.replaceAll("[.]","_")

      selectString +=  ", ${selectList} AS \"${asString}\""
    }


    /* prepare select clause : end */

    /* prepare from clause : start */
    /*fromString  = "  setupx.entity.Experiment E, setupx.entity.ExperimentClass C, setupx.entity.ClassSample S,";
    fromString += "  setupx.entity.ClassSpecies CS, setupx.entity.ClassOrgan CO, setupx.entity.SampleChromatof SC,";
    fromString += "  setupx.entity.ExperimentPublication PUB " */

    fromString  = " experiment E left join experimentclass C on E.experiment_id = c.experiment_id  left join classsample S on c.experimentclass_id = s.experimentclass_id left join  classspecies CS on C.experimentclass_id = cs.experimentclass_id"
    fromString += " left join classorgan CO on c.experimentclass_id = CO.experimentclass_id left join samplechromatof SC on s.classsample_id = sc.class_sample_id left join samplemetadata SM on S.classsample_id = SM.class_sample_id"
    fromString += " left join experimentpublication PUB on e.experiment_id = pub.experiment_id"

    /* prepare from clause : end */

    /* where clause regarding joins : srart */
    /*whereString  = " ( E.experiment_id = C.experiment_id AND C.class_id = S.class_id AND C.class_id = CS.class_id "
    whereString += "AND C.class_id = CO.class_id AND S.sample_id = SC.sample_id AND E.experiment_id = PUB.experiment_id ) " */
    /* where clause regarding joins : end */
    /* prepare from clause : end */

    
    /* prepare where clause : start */
    def objectDBTypeMap = createObjectDBTypeMap()
    int bracketsCount=0
    whereClauseVOList.each {  WhereClauseVO whereVO->

      if(whereString.trim().length() ==0){
        whereString = " ${prepareSearchCriteriaSubQuery(whereVO.getCriteria(),whereVO.getOperation(),whereVO.getSearchData(),objectDBTypeMap)} "
        //whereString = " ( upper(${whereVO.getCriteria()})"+" ${whereVO.getOperation()} upper("+ setQueryParams(whereVO.getSearchData(), objectDBTypeMap.getAt(criteriaString))+")"
      }else{

        if(whereVO?.getOption()!=null && !whereVO?.getOption().trim().equalsIgnoreCase("-1")) {
          whereString += " ${whereVO.getOption()}"
        }else{
          whereString += " AND "
        }

        whereString += " ${prepareSearchCriteriaSubQuery(whereVO.getCriteria(),whereVO.getOperation(),whereVO.getSearchData(),objectDBTypeMap)} "

        //whereString += " ( upper(${whereVO.getCriteria()})"+" ${whereVO.getOperation()} upper("+ setQueryParams(whereVO.getSearchData(), objectDBTypeMap.getAt(criteriaString))+")"
      }

      bracketsCount++;
    }

    // to close all opened bresses 
    for(int index=0; index<bracketsCount ; index++){
      whereString += " ) ";
    }
    /* prepare where clause : end */

    /* prepare order by clause : start*/
    orderByClauseVOList.each {OrderByClauseVO orderVO->
      if(orderByString.trim().length() ==0){
        orderByString = " ${orderVO.getOrderBy()}"+"  ${orderVO.getOrderType()} "
      }else{
        orderByString += ", ${orderVO.getOrderBy()}"+"  ${orderVO.getOrderType()} "
      }
    }
    /* prepare order by clause : end */

    gLogger.info "selectString :: ${selectString}"
    gLogger.info "fromString :: ${fromString}"
    gLogger.info "whereString :: ${whereString}"
    gLogger.info "orderByString :: ${orderByString}"

    String Query = "";
    if(selectString.trim().length() !=0){
      Query += " SELECT ${selectString}\n"
    }

    Query += " FROM ${fromString}\n"
    
    if(whereString.trim().length() !=0){
      Query += " WHERE ${whereString}\n"
    }

    if(orderByString.trim().length() !=0){
      Query += " ORDER BY ${orderByString}"
    }

    gLogger.info "\n=======================\nQuery ::\n${Query}\n\n======================="

    def searchResult = executeQueryService.executeSqlQuery(Query)
    gLogger.info "Result Count :: ${searchResult?.size()}"

    gLogger.info "Out from processQuery method of GenericSearchService"
    return searchResult
  }

    /**
     * to prepare sub-query for where clause
     * @param criteria
     * @param operation
     * @param searchData
     * @param objectDBTypeMap
     * @return String
     */
  private String prepareSearchCriteriaSubQuery(String criteria,String operation,String searchData,def objectDBTypeMap){
      String subQuery = ""
      String criteriaString = criteria.toUpperCase()
      criteriaString = criteriaString.replaceAll("[.]","_")

      if(criteriaString.equalsIgnoreCase("CS_SPECIES_NAME")){
          def uniqueSxSpeciesNCBIIds = speciesService.getUniqueSxSpeciesNCBIIds()
          def params = ["uniqueSxSpeciesNCBIIds":uniqueSxSpeciesNCBIIds]

          def nodesList = taxonomyService.searchTaxonomyByTaxonomyNameWithWildCards(searchData,params)
          gLogger.info "nodesList :: ${nodesList}"
          String tempSearchData = ""
          if( nodesList!=null ){
              def nameCheckList=[]
              if(nodesList instanceof Collection && nodesList.size() >0 ){
                nodesList.each {TaxonomyNodes node->
                    def allNames = taxonomyService.getAllNamesByTaxonomyId(node.id)
                    gLogger.info "allNames :: ${allNames}"
                    if( allNames!=null ){
                        if( allNames instanceof  Collection && allNames.size()>0 ){
                            allNames.each{ name->
                                if( nameCheckList!=null && !nameCheckList.contains(name.toString().toUpperCase()) ){
                                    nameCheckList.add(name)
                                    if(tempSearchData.length()==0){
                                        tempSearchData = "upper("+ setQueryParams(name, objectDBTypeMap.getAt(criteriaString))+")"
                                    }else{
                                        tempSearchData += ", upper("+ setQueryParams(name, objectDBTypeMap.getAt(criteriaString))+")"
                                    }
                                }
                            }
                        }
                    }
                }
              }
          }

          subQuery = "  ( upper(${criteria})"+" in ( " + tempSearchData+" ) "
      }else{
        subQuery = "  ( upper(${criteria})"+" ${operation} upper("+ setQueryParams(searchData, objectDBTypeMap.getAt(criteriaString))+")"
      }

      return subQuery
  }

    /**
     * prepare where clause to view
     * @param whereClauseVOList
     * @return
     */
  def getWhereClauseString(def whereClauseVOList){
    
    gLogger.info "Coming into getWhereClauseString method of GenericSearchService"
    def whereString = ""
    def objectDBTypeMap = createObjectDBTypeMap()
    def ColumnsTitleMap = this.createColumnsTitleMap()
    
    int bracketsCount=0
    whereClauseVOList.each {  WhereClauseVO whereVO->
      String criteriaString = whereVO.getCriteria().toUpperCase()
      criteriaString = criteriaString.replaceAll("[.]","_")

      if(whereString.trim().length() ==0) {
        whereString = "\t\t( <b> ${ColumnsTitleMap.getAt(criteriaString)} </b>"+" ${whereVO.getOperation()} "+ setQueryParams(whereVO.getSearchData(), objectDBTypeMap.getAt(criteriaString))
      }else{

        if(whereVO?.getOption()!=null && !whereVO?.getOption().trim().equalsIgnoreCase("-1")) {
          whereString += " ${whereVO.getOption()}"
        }else{
          whereString += "\n\tAND\t "
        }

        whereString += " ( <b> ${ColumnsTitleMap.getAt(criteriaString)} </b>"+" ${whereVO.getOperation()} "+ setQueryParams(whereVO.getSearchData(), objectDBTypeMap.getAt(criteriaString))
      }

      bracketsCount++;
    }

    // to close all opened bresses
    for(int index=0; index<bracketsCount ; index++){
      whereString += " ) ";
    }
    /* prepare where clause : end */

    gLogger.info "Out from getWhereClauseString method of GenericSearchService"
    return whereString.toString()
  }

    /**
     * prepare query param
     * @param value
     * @param dbType
     * @return
     */
  private String setQueryParams(def value,String dbType){
    gLogger.info "Coming into setQueryParams method of GenericSearchService"
    String queryParams = "''"

    if(value!=null && value instanceof String){
      value = value.toString().replaceAll("[']","''")
    }

    gLogger.info "dbType :: ${dbType}, value :: ${value}"

    if( dbType!=null ){
      if( dbType.equalsIgnoreCase("VARCHAR") || dbType.equalsIgnoreCase("NVARCHAR") || dbType.equalsIgnoreCase("CHAR") || dbType.equalsIgnoreCase("NCHAR") ){
        queryParams = "'"+wildCard.convertToLikeWildCard(value)+"'";
      }else if ( dbType.equalsIgnoreCase("NUMBER") || dbType.equalsIgnoreCase("NUMERIC") || dbType.equalsIgnoreCase("INT") || dbType.equalsIgnoreCase("INTEGER") ){
        queryParams = value;
      }
    }
    gLogger.info "queryParams :: ${queryParams}"
    gLogger.info "Out from setQueryParams method of GenericSearchService"
    return queryParams
  }


  /**
   * prepare generic result for export and listing
   * isExport = false -> is used for listing and
   * isExport = true -> is used for export
   * @param resultList
   * @param selectList
   * @param params
   * @param isExport
   * @return
   */
  def prepareExperimentsResult(def resultList,def selectList,Map params = [:],boolean isExport = false){

    gLogger.info "comming into prepareExperimentResult method of GenericSearchService"
    gLogger.info "resultList :: ${resultList?.size()}"
    List fieldList = ["SrNo","id"]
    Map labelMap = ["SrNo":"Sr. No.","id":"Experiment Id"]
    Map formatters = [:]
    Map parameters = [:]
    Map cssClassMap = ["SrNo":"srno-no-break","id":"text-no-break"]
    List formatedList = null
    List uniqueList = null


    // prepare field list
    def ColumnsTitleMap = this.createColumnsTitleMap()

    if(selectList!=null){
      if(selectList instanceof String[] || selectList instanceof Collection){
        selectList.each {column->
          String col = column.toString().trim().toUpperCase()
          col = col.replaceAll("[.]","_")

          fieldList.add( col )

          if(labelMap.getAt(col) ==null ){
            labelMap.put(col, ColumnsTitleMap.getAt(col))
            cssClassMap.putAt(col,"text-with-break")
          }
        }
      }else{
          String col = selectList.toString().trim().toUpperCase()
          col = col.replaceAll("[.]","_")

          fieldList.add( col )

          if(labelMap.getAt(col) ==null ){
            labelMap.put(col, ColumnsTitleMap.getAt(col))
            cssClassMap.putAt(col,"text-with-break")
          }
      }

    }

    if( params.format == "xml" ){
      labelMap = createLabelMapForXML(fieldList)
    }

    gLogger.info "fieldList :: ${fieldList}"
    gLogger.info "labelMap :: ${labelMap}"

    if (resultList != null && resultList.size() > 0) {
      formatedList = new ArrayList()
      uniqueList = new ArrayList()
      long cnt = 0;

      //if(!isExport){
        if( params?.offset != null )
        {
            cnt = Long.parseLong("0"+params?.offset.toString());
        }
      //}else{
        parameters = [title: "Generic Search Result"]
      //}

      resultList.each { row->
        if( row!=null){

          //System.out.println(row)

          def id = row.getAt("EXPERIMENT_ID")?.toString()

//          if(isExport){
//
//            // Logic is Panding
//
//          }else{
          
            // Start : Create dynamic link
           // def linkExperiment = g.link([controller:"search",action:"showInfo",params:[infoBy:"ExperimentId",id:"${id}"]]) { id } // not in use
            //gLogger linkId
            // End : Create dynamic link
            String uniqueString = "";
            def resultRow = [:]
            uniqueString = id;
            if(selectList!=null){
              String columnName = "";
              if(selectList instanceof String[] || selectList instanceof Collection){
                selectList.each {column->
                  columnName = column.toString().trim().toUpperCase();
                  String columnString = columnName.replaceAll("[.]","_")

                  if(resultRow.getAt(columnString) == null ){
                    resultRow.put(columnString, row.getAt(columnString)?.toString())
                    uniqueString += "<>"+row.getAt(columnString)?.toString()
                  }
                }
              }else{
                  columnName = selectList.toString().trim().toUpperCase();
                  String columnString = columnName.replaceAll("[.]","_")

                  if(resultRow.getAt(columnString) == null ){
                    resultRow.put(columnString, row.getAt(columnString)?.toString())
                    uniqueString += "<>"+row.getAt(columnString)?.toString()
                  }
              }
            }

            if(uniqueList.indexOf(uniqueString) == -1){
              resultRow.put("SrNo",++cnt)
              //resultRow.put("id",linkExperiment)
              resultRow.put("id",id)
              uniqueList.add( uniqueString )
              formatedList.add( resultRow )
            }
          //}
        }
      }

    }
    //println formatedList
    gLogger.info formatedList?.size

    String ErrorMsg = "";
    if( formatedList == null || formatedList?.size == 0 ){
      ErrorMsg = " No Record Found"
    }

    gLogger.info "Out from prepareExperimentResult method of GenericSearchService"
    return ["fieldList":fieldList,"labelMap":labelMap,"formatters":formatters,"parameters":parameters,"cssClassMap":cssClassMap,"resultList":formatedList,"ErrorMsg":ErrorMsg]
  }

  /**
   * create lable map for export, b'coz in case of export to xml blank spaces are not allowed
   * @param fieldList
   * @return
   */
  private Map createLabelMapForXML(List fieldList){
    Map labelMap = [:]
    fieldList.each { str ->
       labelMap.put(str,str)
    }
    return labelMap
  }

    /**
     * prepare TitleMap
     * @return
     */
  def createColumnsTitleMap(){

    gLogger.info "Comming into createColumnsTitleMap method of GenericSearchService"
    def SelectList = this.getAllSelectOptions()
    def ColumnsTitleMap

    if(SelectList!=null){

      ColumnsTitleMap = new HashMap();

      SelectList.each {column->
        ColumnsTitleMap.putAt "${column.groupkey.toString().toUpperCase()}_${column.db_object.toString().toUpperCase()}", column.title!=null && column.title.toString().trim().length() > 0 ? column.title : column.db_object
      }
    }

    gLogger.info "ColumnsTitleMap :: ${ColumnsTitleMap}"

    gLogger.info "Out from createColumnsTitleMap method of GenericSearchService"
    return ColumnsTitleMap

  }

    /**
     * prepare DBType map
     * @return
     */
  def createObjectDBTypeMap(){

    gLogger.info "Comming into createObjectDBTypeMap method of GenericSearchService"
    def SelectList = this.getAllSelectOptions()
    def ObjectDBTypeMap

    if(SelectList!=null){

      ObjectDBTypeMap = new HashMap();

      SelectList.each {column->
        ObjectDBTypeMap.putAt "${column.groupkey.toString().toUpperCase()}_${column.db_object.toString().toUpperCase()}", column.objectDbType!=null && column.objectDbType.toString().trim().length() > 0 ? column.objectDbType : null
      }
    }

    gLogger.info "ObjectDBTypeMap :: ${ObjectDBTypeMap}"

    gLogger.info "Out from createObjectDBTypeMap method of GenericSearchService"
    return ObjectDBTypeMap

  }

}
